﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using org.maquech.CEeL.Core.EducationalProgram;

namespace org.maquech.CEeL.Core.People {
    public sealed class Teacher : Person {
        #region Fields and Properties

        /// <summary>
        /// The Teacher's registration id field.
        /// </summary>
        private bool active;

        /// <summary>
        /// Teacher's registration id.
        /// </summary>
        public bool Active {
            get {
                return this.active;
            }
            set {
                if ( this.active != value )
                    modifiedDBFields.Add( "active" );
                this.active = value;
            }
        }


        /// <summary>
        /// The Teachers's admission date field.
        /// </summary>
        private AcademicDegree degree;

        /// <summary>
        /// The Teachers's admission date
        /// </summary>
        public AcademicDegree Degree {
            get {
                return this.degree;
            }
            set {
                if ( this.degree != value )
                    modifiedDBFields.Add( "degree_id" );
                this.degree = value;
            }
        }

        /// <summary>
        /// Checks if the current instance is valid to be stored in the database.
        /// </summary>
        public override bool IsValid {
            get {
                return base.IsValid && degree.IsValid;
            }
        }
        #endregion

        #region Constructors
        public Teacher ( )
            : base( ) {
            this.active = true;
            this.degree = new AcademicDegree( );
        }

        public Teacher ( int id, string name, string middle_name, string patronymic, string matronymic, DateTime dateOfBirth, string pictureFile,
            bool active, AcademicDegree degree )
            : base( id, name, middle_name, patronymic, matronymic, dateOfBirth, pictureFile ) {
            this.active = active;
            this.degree = degree;
        }


        public Teacher ( int id, string name, string middle_name, string patronymic, string matronymic, DateTime dateOfBirth, bool active, AcademicDegree degree )
            : this( id, name, middle_name, patronymic, matronymic, dateOfBirth, string.Empty, active, degree ) {
        }

        public Teacher ( string name, string middle_name, string patronymic, string matronymic, DateTime dateOfBirth, bool active, AcademicDegree degree )
            : this( -1, name, middle_name, patronymic, matronymic, dateOfBirth, string.Empty, active, degree ) {
        }

        public Teacher ( string name, string middle_name, string patronymic, string matronymic, DateTime dateOfBirth, string pictureFile, bool active, AcademicDegree degree )
            : this( -1, name, middle_name, patronymic, matronymic, dateOfBirth, pictureFile, active, degree ) {
        }
        #endregion

        #region Methods

        #region System.Object Methods
        /// <summary>
        /// Checks if the object is equal to the current instance.
        /// </summary>
        /// <param name="obj">The object to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public override bool Equals ( object obj ) {
            if ( obj == null ) {
                return false;
            }
            Teacher t = obj as Teacher;
            if ( ( object ) t == null ) {
                return false;
            }
            return base.Equals( obj )
                && ( this.active == t.active )
                && ( this.degree == t.degree );
        }

        /// <summary>
        /// Checks if the Teacher is equal to the current instance.
        /// </summary>
        /// <param name="a">The Teacher to check for equality</param>
        /// <returns><code>True</code> if the objects are equal, <code>false</code> otherwise.</returns>
        public bool Equals ( Teacher t ) {
            if ( ( object ) t == null ) {
                return false;
            }
            return base.Equals( t )
                && ( this.active == t.active )
                && ( this.degree == t.degree );
        }

        public override int GetHashCode ( ) {
            return base.GetHashCode( )
                ^ this.active.GetHashCode( )
                ^ this.degree.GetHashCode( );
        }

        /// <summary>
        /// String representation of the current instance.
        /// </summary>
        /// <returns>A string representation of the current instance.</returns>
        public override string ToString ( ) {
            StringBuilder bldr = new StringBuilder( );
            object [ ] objArr = new object [ ] { this.active, this.degree, base.ToString( ) };
            bldr.AppendFormat( "{1} {2} [{0}]", objArr );
            return bldr.ToString( );
        }
        #endregion

        /// <summary>
        /// Saves the current instance to the database.
        /// </summary>
        /// <returns><code>True</code> if the object was saved to the database, <code>false</code> otherwise.</returns>
        public override bool Save ( ) {
            bool saved = false;
            bool personSaved = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;
            bool teacherIsNew = false;
            bool isNew = false;
            using ( DbConnection connection = db.CreateConnection( ) ) {
                connection.Open( );
                DbTransaction transaction = connection.BeginTransaction( );
                try {
                    sqlCommand = "SELECT * FROM "
                        + " (SELECT count(*) > 0 AS teacher_exists FROM teachers WHERE id = @id) AS t,"
                        + " (SELECT count(*) > 0 AS person_exists FROM persons WHERE id = @id) AS p";
                    dbCommand = db.GetSqlStringCommand( sqlCommand );
                    db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                    using ( IDataReader rdr = db.ExecuteReader( dbCommand, transaction ) ) {
                        if ( rdr.Read( ) ) {
                            isNew = ( bool ) rdr [ "person_exists" ];
                            teacherIsNew = ( bool ) rdr [ "teacher_exists" ];
                        }
                    }
                    if ( teacherIsNew ) {
                        sqlCommand = "SELECT nextval('persons_id_seq')";
                        dbCommand = db.GetSqlStringCommand( sqlCommand );
                        this.id = ( int ) db.ExecuteScalar( dbCommand, transaction );

                        sqlCommand = "INSERT INTO persons ( id, name, middle_name, patronymic, matronymic, dob, picture_file )"
                           + " VALUES ( @id, @name, @middle_name, @patronymic, @matronymic, @dob, @picture_file )";
                        dbCommand = db.GetSqlStringCommand( sqlCommand );
                        db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                        db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                        db.AddInParameter( dbCommand, "@middle_name", DbType.String, this.middleName );
                        db.AddInParameter( dbCommand, "@patronymic", DbType.String, this.patronymic );
                        db.AddInParameter( dbCommand, "@matronymic", DbType.String, this.matronymic );
                        db.AddInParameter( dbCommand, "@dob", DbType.DateTime, this.dateOfBirth );
                        db.AddInParameter( dbCommand, "@picture_file", DbType.String, this.pictureFile );
                        personSaved = db.ExecuteNonQuery( dbCommand, transaction ) > 0;
                    }
                    else if ( this.modifiedDBFields.Count > 0 ) {
                        sqlCommand = "UPDATE persons SET name = @name, middle_name = @middle_name, patronymic = @patronymic,"
                            + "  matronymic = @matronymic, dob = @dob, picture_file = @picture_file"
                            + " WHERE id = @id";
                        dbCommand = db.GetSqlStringCommand( sqlCommand );
                        db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                        db.AddInParameter( dbCommand, "@name", DbType.String, this.name );
                        db.AddInParameter( dbCommand, "@middle_name", DbType.String, this.middleName );
                        db.AddInParameter( dbCommand, "@patronymic", DbType.String, this.patronymic );
                        db.AddInParameter( dbCommand, "@matronymic", DbType.String, this.matronymic );
                        db.AddInParameter( dbCommand, "@dob", DbType.DateTime, this.dateOfBirth );
                        db.AddInParameter( dbCommand, "@picture_file", DbType.String, this.pictureFile );
                        personSaved = db.ExecuteNonQuery( dbCommand, transaction ) > 0;
                        //Remove udpated dbfields
                        foreach ( string dbField in base.DBFieldNames ) {
                            modifiedDBFields.Remove( dbField );
                        }
                    }
                    else {
                        //No update or insert needed for person
                        personSaved = true;
                    }
                    if ( personSaved ) {
                        if ( isNew ) {
                            sqlCommand = "INSERT INTO teachers ( id, active, degree_id )"
                               + " VALUES ( @id, @active, @degree_id )";
                            dbCommand = db.GetSqlStringCommand( sqlCommand );
                            db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                            db.AddInParameter( dbCommand, "@active", DbType.Boolean, this.active );
                            db.AddInParameter( dbCommand, "@degree_id", DbType.Int32, this.degree.Id );
                            saved = db.ExecuteNonQuery( dbCommand, transaction ) > 0;
                        }
                        else if ( this.modifiedDBFields.Count > 0 ) {
                            sqlCommand = "UPDATE teachers SET active = @active, degree_id = @degree_id"
                                + " WHERE id = @id";
                            dbCommand = db.GetSqlStringCommand( sqlCommand );
                            db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                            db.AddInParameter( dbCommand, "@active", DbType.Boolean, this.active );
                            db.AddInParameter( dbCommand, "@degree_id", DbType.Int32, this.degree.Id );
                            saved = db.ExecuteNonQuery( dbCommand, transaction ) > 0;
                            //Remove udpated dbfields
                            foreach ( string dbField in this.DBFieldNames ) {
                                modifiedDBFields.Remove( dbField );
                            }
                        }
                    }
                    else {
                    }
                    transaction.Commit( );
                    saved = true;
                }
                catch ( Exception e ) {
                    transaction.Rollback( );
                    saved = false;
                    this.errorMessages.Add( "There was an error saving the Teacher: " + e.Message );
                }
            }

            return saved;
        }

        /// <summary>
        /// Deletes the current object from the database.
        /// </summary>
        /// <returns><code>True</code> if the object was deleted from the database, <code>false</code> otherwise.</returns>
        public override bool Delete ( ) {
            bool deleted = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand;
            try {
                sqlCommand = "DELETE FROM teachers WHERE id = @id";
                dbCommand = db.GetSqlStringCommand( sqlCommand );
                db.AddInParameter( dbCommand, "@id", DbType.Int32, this.id );
                deleted = db.ExecuteNonQuery( dbCommand ) >= 0;
            }
            catch ( Exception e ) {
                deleted = false;
                this.errorMessages.Add( "There was an error saving the Teacher: " + e.Message );
            }
            return deleted;
        }


        #region Load Methods
        /// <summary>
        /// Loads the Teacher with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The Teachers' id.</param>
        /// <returns>A Teacher object with it's information or a null Teacher object if the <code>id</code> was not present in the database.</returns>
        public static new Teacher Load ( int id ) {
            Teacher t = null;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT p.name, p.middle_name, p.patronymic, p.matronymic, p.dob, p.picture_file,"
                + " t.active, t.degree_id, a.name AS name_as, a.abbreviation AS abbreviation_as, a.ordering AS ordering_ad "
                + " FROM persons AS p, teachers AS t, academic_degrees AS a WHERE t.id = @id AND t.id = p.id AND a.id = t.degree_id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            try {
                using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                    if ( rdr.Read( ) ) {
                        t = new Teacher( id, ( string ) rdr [ "name" ], rdr [ "middle_name" ] as string, ( string ) rdr [ "patronymic" ],
                            ( string ) rdr [ "matronymic" ], ( DateTime ) rdr [ "dob" ], ( string ) rdr [ "picture_file" ],
                            ( bool ) rdr [ "active" ], 
                            new AcademicDegree( ( int ) rdr [ "degree_id" ], ( string ) rdr [ "name_ad" ],
                                ( string ) rdr [ "abbreviation" ],  ( int ) rdr [ "ordering" ] ) );
                        t.modifiedDBFields.Clear( );
                        t.ClearMessages( );
                        t.isNew = false;
                    }
                    rdr.Close( );
                }
            }
            catch ( Exception e ) {
                t.errorMessages.Add( string.Format( "There was an error loading the Teacher with ID {0}: {1}", id.ToString( ), e.Message ) );
            }
            return t;
        }

        /// <summary>
        /// Loads all of the Teachers in the database to a list.
        /// </summary>
        /// <returns>A List containing all of the Teachers on the database.</returns>
        public new static List<Teacher> LoadAll ( ) {
            List<Teacher> tl = new List<Teacher>( );
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            //TODO: hacer vistas de este tipo de queries.
            string sqlCommand = "SELECT p.id, p.name, p.middle_name, p.patronymic, p.matronymic, p.dob, p.picture_file,"
                + " t.active, t.degree_id, a.name AS name_as, a.abbreviation AS abbreviation_as, a.ordering AS ordering_ad "
                + " FROM persons AS p, teachers AS t, academic_degrees AS a WHERE t.id = @id AND t.id = p.id AND a.id = t.degree_id"
                + " ORDER BY p.patronymic, m.matronymic, p.name";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            using ( IDataReader rdr = db.ExecuteReader( dbCommand ) ) {
                while ( rdr.Read( ) ) {
                    tl.Add( new Teacher( ( int ) rdr [ "id" ], ( string ) rdr [ "name" ], rdr [ "middle_name" ] as string, ( string ) rdr [ "patronymic" ],
                            ( string ) rdr [ "matronymic" ], ( DateTime ) rdr [ "dob" ], ( string ) rdr [ "picture_file" ],
                             ( bool ) rdr [ "active" ],
                            new AcademicDegree( ( int ) rdr [ "degree_id" ], ( string ) rdr [ "name_ad" ],
                                ( string ) rdr [ "abbreviation" ], ( int ) rdr [ "ordering" ] ) ) );
                }
                rdr.Close( );
            }
            return tl;
        }

        /// <summary>
        /// Checks the database for existence of the Teacher with the given <code>id</code>.
        /// </summary>
        /// <param name="id">The Teachers' id.</param>
        /// <returns><code>True</code> if the person with the given <code>id</code>exists, <code>false</code> otherwise.</returns>
        public new static bool Exists ( int id ) {
            bool exists = false;
            Database db = DatabaseFactory.CreateDatabase( );
            DbCommand dbCommand;
            string sqlCommand = "SELECT count(*) > 0 as exists FROM teachers WHERE id = @id";
            dbCommand = db.GetSqlStringCommand( sqlCommand );
            db.AddInParameter( dbCommand, "@id", DbType.Int32, id );
            exists = ( bool ) db.ExecuteScalar( dbCommand );
            return exists;
        }

        #endregion

        #endregion
    }
}

//Hey! Teachers! Leave the kids alone!